﻿<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true"
    CodeBehind="Loan.aspx.cs" Inherits="Project_Udrinkwater.Loan_Invoice.Loan" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <dx:ASPxRoundPanel ID="RPN_SalaryEmp" runat="server" Width="100%" 
         HeaderText="รายการยืม - คืน">
        <HeaderStyle Font-Size="Medium" HorizontalAlign="Left" /></dx:ASPxRoundPanel>
    <dx:ASPxGridView ID="ASPxGridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataLoan"
        KeyFieldName="Loan_ID" Width="100%">
        <Columns>
            <dx:GridViewCommandColumn VisibleIndex="6" Caption="ทำรายการ">
                <EditButton Visible="True" Text="แก้ไข">
                </EditButton>
                <NewButton Visible="True" Text="เพิ่ม">
                </NewButton>
                <DeleteButton Visible="True" Text="ลบ">
                </DeleteButton>
                <ClearFilterButton Visible="True">
                </ClearFilterButton>
            </dx:GridViewCommandColumn>
            <dx:GridViewDataTextColumn FieldName="Loan_ID" ReadOnly="True" VisibleIndex="0" Caption="ลำดับ">
                <EditFormSettings Visible="False" />
<EditFormSettings Visible="False"></EditFormSettings>
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataTextColumn FieldName="Loan_doc_NO" ReadOnly="True" VisibleIndex="1"
                Caption="เลขที่">
                <EditFormSettings Visible="False" />
<EditFormSettings Visible="False"></EditFormSettings>
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataComboBoxColumn FieldName="Employee_ID" VisibleIndex="2" Caption="ชื่อพนักงาน">
                <PropertiesComboBox DataSourceID="SqlDataEmpID" TextField="Emp" 
                    ValueField="Employee_ID">
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataDateColumn FieldName="Loan_Date" VisibleIndex="4" 
                Caption="วันที่">
                <PropertiesDateEdit DisplayFormatString="dd/MM/yyy">
                </PropertiesDateEdit>
            </dx:GridViewDataDateColumn>
            <dx:GridViewDataComboBoxColumn Caption="ชื่อลูกค้า" FieldName="Customer_ID" 
                VisibleIndex="3">
                <PropertiesComboBox DataSourceID="SqlDataCustomer_ID" TextField="FullName" 
                    ValueField="Customer_ID">
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataTextColumn Caption="พิมพ์ใบเสร็จ" VisibleIndex="5">
                <EditFormSettings Visible="False" />
                <DataItemTemplate>
                    <asp:LinkButton ID="LinkButton1"  CommandArgument ='<%# Eval("Loan_ID")%>' OnCommand="LinkButton1_Command" runat="server">พิมพ์ใบเสร็จการยืม-คืน</asp:LinkButton>
                </DataItemTemplate>
                <CellStyle HorizontalAlign="Center" Wrap="True">
                </CellStyle>
            </dx:GridViewDataTextColumn>
        </Columns>
        <Settings ShowFilterRow="True" />
        <SettingsDetail ShowDetailRow="True" />

<Settings ShowFilterRow="True"></Settings>

<SettingsDetail ShowDetailRow="True"></SettingsDetail>

        <Templates>
            <DetailRow>
                <dx:ASPxGridView ID="ASPxGridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataLoanList"
                    OnBeforePerformDataSelect="ASPxGridView2_BeforePerformDataSelect" 
                    KeyFieldName="ID" Width="100%">
                    <TotalSummary>
                        <dx:ASPxSummaryItem DisplayFormat="รวม {0:#,###.00}" FieldName="Total" ShowInColumn="Total"
                            SummaryType="Sum" ShowInGroupFooterColumn="Total" />
                    </TotalSummary>
                    <Columns>
                        <dx:GridViewCommandColumn VisibleIndex="11" Caption="ทำรายการ">
                            <EditButton Text="แก้ไข" Visible="True">
                            </EditButton>
                            <NewButton Visible="True" Text="เพิ่ม">
                            </NewButton>
                            <DeleteButton Text="ลบ" Visible="True">
                            </DeleteButton>
                            <CancelButton Visible="True">
                            </CancelButton>
                            <ClearFilterButton Visible="True">
                            </ClearFilterButton>
                        </dx:GridViewCommandColumn>
                        <dx:GridViewDataTextColumn FieldName="ID" VisibleIndex="0" ReadOnly="True" 
                            Visible="False">
                            <EditFormSettings Visible="False" />
                        </dx:GridViewDataTextColumn>
                        <dx:GridViewDataTextColumn FieldName="Loan_ID" VisibleIndex="1" Visible="False">
                            <EditFormSettings Visible="False" />
                        </dx:GridViewDataTextColumn>
                        <dx:GridViewDataTextColumn FieldName="RowID" VisibleIndex="2" ReadOnly="True" 
                            Caption="ลำดับ">
                            <EditFormSettings Visible="False" />
                        </dx:GridViewDataTextColumn>
                        <dx:GridViewDataTextColumn FieldName="Loan_Amount" VisibleIndex="7" 
                            Caption="จำนวน">
                        </dx:GridViewDataTextColumn>
                        <dx:GridViewDataComboBoxColumn FieldName="Loan_Unit" VisibleIndex="6" 
                            Caption="ประเภท คืน/ยืม">
                            <PropertiesComboBox DataSourceID="SqlDataLoan_Category" TextField="Loan_Name" ValueField="Loan_Unit"
                                ValueType="System.Int32"></PropertiesComboBox>
                        </dx:GridViewDataComboBoxColumn>
                        <dx:GridViewDataComboBoxColumn FieldName="Product_ID" VisibleIndex="5" 
                            Caption="ชนิดสินค้า ( ถัง/ลัง )">
                            <PropertiesComboBox DataSourceID="SqlDataProduct_ID" TextField="Product_Name" 
                                ValueField="Product_ID"></PropertiesComboBox>
                        </dx:GridViewDataComboBoxColumn>
                        <dx:GridViewDataTextColumn FieldName="Price" VisibleIndex="8" 
                            Caption="ราคาต่อหน่วย">
                            <EditFormSettings Visible="False" />
                        </dx:GridViewDataTextColumn>
                        <dx:GridViewDataTextColumn FieldName="Total" VisibleIndex="9" Caption="รวม">
                            <EditFormSettings Visible="False" />
                        </dx:GridViewDataTextColumn>
                    </Columns>
                    <Settings ShowFilterRow="True" ShowFooter="True" />
                </dx:ASPxGridView>
            </DetailRow>
        </Templates>
    </dx:ASPxGridView>
    <asp:SqlDataSource ID="SqlDataLoan" runat="server" ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>"
        SelectCommand="SELECT TB_Loan.* FROM TB_Loan" DeleteCommand="DELETE FROM TB_Loan WHERE (Loan_ID = @Loan_ID)"
        InsertCommand="INSERT INTO TB_Loan(Employee_ID, Loan_Date, Customer_ID) VALUES (@Employee_ID, @Loan_Date, @Customer_ID)"
        
        UpdateCommand="UPDATE TB_Loan SET Employee_ID = @Employee_ID, Loan_Date = @Loan_Date, Customer_ID = @Customer_ID WHERE (Loan_ID = @Loan_ID)">
        <DeleteParameters>
            <asp:Parameter Name="Loan_ID" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="Employee_ID" />
            <asp:Parameter Name="Loan_Date" />
            <asp:Parameter Name="Customer_ID" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="Employee_ID" />
            <asp:Parameter Name="Loan_Date" />
            <asp:Parameter Name="Customer_ID" />
            <asp:Parameter Name="Loan_ID" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataEmpID" runat="server" ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>"
        
        SelectCommand="SELECT Employee_ID, FirstName + ' ' + LastName AS Emp FROM TB_Employee"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataLoanList" runat="server" ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>"
        SelectCommand="SELECT TB_Loan_List.ID,
TB_Loan_List.Loan_ID, 
ROW_NUMBER() OVER (ORDER BY TB_Loan_List.Loan_ID Asc) AS RowID, 
TB_Loan.Loan_doc_NO, TB_Loan_List.Loan_Amount, 
TB_Loan_List.Loan_Unit, 
TB_Loan_List.Product_ID,
TB_Product.Product_Name,
TB_Order_Category.Price, 
TB_Loan_List.Loan_Amount * TB_Loan_List.Loan_Unit * TB_Order_Category.Price AS Total 
FROM TB_Loan_List 
INNER JOIN TB_Loan ON TB_Loan_List.Loan_ID = TB_Loan.Loan_ID 
INNER JOIN TB_Product ON TB_Loan_List.Product_ID = TB_Product.Product_ID 
INNER JOIN TB_Order_Category ON TB_Product.Product_ID = TB_Order_Category.Product_ID
WHERE TB_Loan_List.Loan_ID = @Loan_ID AND TB_Order_Category.Order_Category_Name = 'ยืม-คืน'"
        DeleteCommand="DELETE FROM TB_Loan_List WHERE (ID = @ID)" InsertCommand="INSERT INTO TB_Loan_List(Loan_ID, Loan_Amount, Loan_Unit, Product_ID) VALUES (@Loan_ID, @Loan_Amount, @Loan_Unit, @Product_ID)"
        
        
        
        
        
        UpdateCommand="UPDATE TB_Loan_List SET Loan_Amount = @Loan_Amount, Loan_Unit = @Loan_Unit, Product_ID = @Product_ID WHERE (ID = @ID)">
        <DeleteParameters>
            <asp:Parameter Name="ID" />
        </DeleteParameters>
        <InsertParameters>
            <asp:SessionParameter Name="Loan_ID" SessionField="Loan_ID" />
            <asp:Parameter Name="Loan_Amount" />
            <asp:Parameter Name="Loan_Unit" />
            <asp:Parameter Name="Product_ID" />
        </InsertParameters>
        <SelectParameters>
            <asp:SessionParameter Name="Loan_ID" SessionField="Loan_ID" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="Loan_Amount" />
            <asp:Parameter Name="Loan_Unit" />
            <asp:Parameter Name="Product_ID" />
            <asp:Parameter Name="ID" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataProduct_ID" runat="server" ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>"
        
        SelectCommand="SELECT Product_ID, Product_Name FROM TB_Product WHERE (Product_ID = 'PD-1') OR (Product_ID = 'PD-3')">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataLoan_Category" runat="server" ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>"
        SelectCommand="SELECT Loan_Unit, Loan_Name FROM TB_Loan_Category"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataCustomer_ID" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT Customer_ID, Firstname + ' ' + Lastname AS FullName FROM TB_Customer">
    </asp:SqlDataSource>
</asp:Content>
